﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ZhimeiSoft.CharmManager.Server.DAL
{
    public class CustomerServiceItemDAL_Const
    {
        public static readonly string SQL_INSERT_CUSTOMERSERVICEITEM = @"INSERT INTO CUSTOMERSERVICEITEM (CSTATUS, COUNT, CREMAINCOUNT, SERVICEITEMID, CUSTOMERID, CMEMO, CBUYTIME, CSERVICESTARTTIME, 
            CSERVICEENDTIME, CTOTALNEEDPAY, CBUYMONEY) VALUES (@CSTATUS, @COUNT, @CREMAINCOUNT, @SERVICEITEMID, @CUSTOMERID, 
            @CMEMO, @CBUYTIME, @CSERVICESTARTTIME, @CSERVICEENDTIME, @CTOTALNEEDPAY, @CBUYMONEY)";

        public static readonly string SQL_UPDATE_CUSTOMERSERVICEITEM = @"UPDATE CUSTOMERSERVICEITEM SET CSTATUS=@CSTATUS, COUNT=@COUNT, CREMAINCOUNT=@CREMAINCOUNT, SERVICEITEMID=@SERVICEITEMID, 
            CUSTOMERID=@CUSTOMERID, CBUYTIME=@CBUYTIME, CSERVICESTARTTIME=@CSERVICESTARTTIME, CSERVICEENDTIME=@CSERVICEENDTIME, CTOTALNEEDPAY=@CTOTALNEEDPAY, 
            CMEMO=@CMEMO, CBUYMONEY = @CBUYMONEY WHERE CUSTOMERSERVICEITEMID=@CUSTOMERSERVICEITEMID";

        public static readonly string SQL_UPDATE_LASTSERVICETIME = "UPDATE CUSTOMERSERVICEITEM SET CLASTSERVICETIME=@CLASTSERVICETIME WHERE CUSTOMERSERVICEITEMID=@CUSTOMERSERVICEITEMID";

        public static readonly string SQL_DELETE_CUSTOMERSERVICEITEM = "DELETE FROM CUSTOMERSERVICEITEM WHERE CUSTOMERSERVICEITEMID=@CUSTOMERSERVICEITEMID";

        public static readonly string SQL_SELECT_CUSTOMERSERVICEITEM = @"SELECT SERVICEITEM.*,CUSTOMERSERVICEITEM.CUSTOMERSERVICEITEMID, CUSTOMERSERVICEITEM.CSTATUS, 
            CUSTOMERSERVICEITEM.COUNT, CUSTOMERSERVICEITEM.CREMAINCOUNT, CUSTOMERSERVICEITEM.CUSTOMERID, CUSTOMERSERVICEITEM.CMEMO, 
            CUSTOMERSERVICEITEM.CLASTSERVICETIME, CUSTOMERSERVICEITEM.CBUYTIME, CUSTOMERSERVICEITEM.CSERVICESTARTTIME, 
            CUSTOMERSERVICEITEM.CSERVICEENDTIME, CUSTOMERSERVICEITEM.CTOTALNEEDPAY, CUSTOMERSERVICEITEM.CBUYMONEY  
            FROM CUSTOMERSERVICEITEM LEFT JOIN SERVICEITEM ON CUSTOMERSERVICEITEM.SERVICEITEMID=SERVICEITEM.SERVICEITEMID";

        public static readonly string SQL_QUERY_COMPUTER_NEXTRESERVATIONDAY = @"SELECT ISNULL(CDAYS, 1) AS CDAYS FROM CUSTOMERSERVICEITEMRESERVATIONTIME WHERE CSEQNUM >= (
            SELECT ISNULL(COUNT,0) - ISNULL(CREMAINCOUNT, 0) + 1 FROM CUSTOMERSERVICEITEM WHERE CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID) 
            AND CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID ORDER BY CSEQNUM ASC";

        public static readonly string SQL_QUERY_COMPUTER_PRIORRESERVATIONDAY = @"SELECT ISNULL(CDAYS, 1) AS CDAYS FROM CUSTOMERSERVICEITEMRESERVATIONTIME WHERE CSEQNUM < (
            SELECT ISNULL(COUNT,0) - ISNULL(CREMAINCOUNT, 0) + 1 FROM CUSTOMERSERVICEITEM WHERE CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID) 
            AND CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID ORDER BY CSEQNUM DESC";

        public static readonly string SQL_SELECT_CUSTOMERSERVICEITEMCONSUME = "SELECT * FROM CUSTOMERSERVICEITEMCONSUME WHERE CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID";

        public static readonly string SQL_SELECT_CUSTOMERSERVICEITEMCONSUME_RESERVATION = "SELECT * FROM CUSTOMERSERVICEITEMCONSUME WHERE CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID AND RESERVATIONID = @RESERVATIONID";
        /// <summary>
        /// 项目第一次服务
        /// </summary>
        public static readonly string SQL_UPDATE_COMPLETE_CUSTOMERSERVICEITEM_FIRSTSERVICE = @"UPDATE CUSTOMERSERVICEITEM SET CSTATUS = @CSTATUS, CREMAINCOUNT = COUNT - @CREMAINCOUNT,
	        CMEMO = @CMEMO, CLASTSERVICETIME = @CLASTSERVICETIME, CSERVICESTARTTIME = @CLASTSERVICETIME, CSERVICEENDTIME = @CSERVICEENDTIME WHERE CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID ";
        /// <summary>
        /// 项目日常服务
        /// </summary>
        public static readonly string SQL_UPDATE_COMPLETE_CUSTOMERSERVICEITEM = @"UPDATE CUSTOMERSERVICEITEM SET CSTATUS = @CSTATUS, CREMAINCOUNT = CREMAINCOUNT - @CREMAINCOUNT,
	        CMEMO = @CMEMO, CLASTSERVICETIME = @CLASTSERVICETIME WHERE CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID";
        /// <summary>
        /// 插入服务项目明细
        /// </summary>
        public static readonly string SQL_INSERT_COMPLETE_CUSTOMERSERVICEITEM_ITEMCONSUME = @"INSERT INTO CUSTOMERSERVICEITEMCONSUME (CUSTOMERSERVICEITEMID,
	        CONSUMETIME, CMINUSCOUNT, CMEMO, EMPLOYEEID, RESERVATIONID) VALUES (@CUSTOMERSERVICEITEMID, @CONSUMETIME, @CMINUSCOUNT, @CMEMO, @EMPLOYEEID, @RESERVATIONID)";

        public static readonly string SQL_UPDATE_ACCOUNT_MONEY = "UPDATE CUSTOMERSERVICEITEM SET CTOTALNEEDPAY = CTOTALNEEDPAY - @CTOTALNEEDPAY WHERE CUSTOMERSERVICEITEMID=@CUSTOMERSERVICEITEMID";

        public static readonly string SQL_QUERY_CUSTOMERSERVICEITEMCONSUME = "SELECT * FROM CUSTOMERSERVICEITEMCONSUME WHERE CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID";

        public static readonly string SQL_QUERY_CUSTOMERSERVICEITEMCONSUME_RESERVATION = "SELECT * FROM CUSTOMERSERVICEITEMCONSUME WHERE RESERVATIONID = @RESERVATIONID";

        public static readonly string SQL_QUERY_DELTECHECK_CUSTOMSERVICEITEM = "SELECT COUNT(1) FROM RESERVATIONSERVICEITEM WHERE CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID";
    }
}
